package com.cats.bi.sqltool;

import com.cats.bi.sqltool.basic.AbstractClause;
import com.cats.bi.sqltool.basic.AbstractColumn;

/**
 * @author wang jian
 * @Description:
 * @create 2021/4/20
 */
public class SQL {

    private static void appendColumns(StringBuilder sql, Object[] columns) {
        sql.append('(');
        boolean f = true;
        for (Object column : columns) {
            if (f) {
                f = false;
            } else {
                sql.append(", ");
            }
            if (column instanceof String) {
                SQLString.appendColumn(sql, column.toString());
            } else {
                AbstractColumn<?> c = (AbstractColumn) column;
                c.shortName(sql);
            }
        }
        sql.append(") ");
    }

    /**
     * select all from table limt x
     *
     * @see {@link Select}
     */
    public static String select(String table, AbstractClause where, int limit) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        SQLString.appendTable(sql, table);
        sql.append(" WHERE ");
        where.toSql(sql);
        sql.append(" LIMIT ").append(limit);
        sql.append(';');
        return sql.toString();
    }

    /**
     * @param table
     * @param where
     * @param limitOffset
     * @param limit
     * @return
     */
    public static String select(String table, AbstractClause where, int limitOffset, int limit) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        SQLString.appendTable(sql, table);
        sql.append(" WHERE ");
        where.toSql(sql);
        sql.append(" LIMIT ").append(limitOffset).append(',').append(limit);
        sql.append(';');
        return sql.toString();
    }

    public static String select(String table, AbstractClause where) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        SQLString.appendTable(sql, table);
        sql.append(" WHERE ");
        where.toSql(sql);
        sql.append(';');
        return sql.toString();
    }

    public static String dropTable(String table) {
        StringBuilder sql = new StringBuilder("DROP TABLE ");
        SQLString.appendTable(sql, table);
        sql.append(';');
        return sql.toString();
    }

    public static String truncateTable(String table) {
        StringBuilder sql = new StringBuilder("TRUNCATE TABLE ");
        SQLString.appendTable(sql, table);
        sql.append(';');
        return sql.toString();
    }

    public static void main(String[] args) {
//        ViewConfigDTO viewConfigDTO = new ViewConfigDTO();
//
//        /* step 1. 拼装子查询
//        Select select = new Select();
//        Over over = new Over("dwd_claim_case", "claim_id");
//        over.tableAs("uid");
//        select.add(over);
//
//        AbstractClause generalClause = AbstractClause.eq(new Column("1"), 1);
//        List<Object> list = Lists.newArrayList(1,2,3);
//        List<Object> list2 = Lists.newArrayList(1,2,3,4);
//        AbstractClause in = AbstractClause.in(new Column("2"), list);
//        AbstractClause in2 = AbstractClause.in(new Column("3"), list2);
//        generalClause.and(in);
//        generalClause.and(in2);
//
//        select.where(generalClause);
//        System.out.println(select.toString());
//
//        // setp 2. 拼装
//        Select select2 = new Select();
//        Count count = new Count(select, "r", "uid");
//        Column column = new Column(select, "r", "uid");
//        count.as("24");
//        AbstractClause generalClause2 = AbstractClause.eq(new Column("1"), 1);
//        select2.add(column);
//
//        select2.where(generalClause2.and(AbstractClause.eq(new Column("r", "rownum"), 1)));
//
//        Having having = new Having(generalClause2);
//
//        select2.having(having);
//        System.out.println(select2.toString());*/
//        String tepTaba = "a";
//        String tepTabB = "b";
//        Select selectBefore = new Select();
//        DateFormat dateFormatBefore = new DateFormat("buy_date", "%Y-%m");
//        selectBefore.add(dateFormatBefore);
//        dateFormatBefore.as("date");
//        Sum sumBefore = new Sum("sort_sales_count", "gross_profit");
//        sumBefore.as("profit");
//        selectBefore.add(sumBefore);
//        selectBefore.groupBy("date");
//        System.out.println("selectBefore :" + "\n" + selectBefore.toString() + "\n");
//
//
//        Select selectAfter = new Select();
//        DateAdd dateAdd = new DateAdd("buy_date", "1", DateType.MONTH);
//        DateFormat dateFormat = new DateFormat(dateAdd, "%Y-%m");
//        selectAfter.add(dateFormat);
//        dateFormat.as("date");
//        Sum sum = new Sum("sort_sales_count", "gross_profit");
//        sum.as("profit");
//        selectAfter.add(sum);
//        selectAfter.groupBy("date");
//        Expression expressionAfter = new Expression(selectAfter);
//        StringBuilder stringBuilder = new StringBuilder();
//        expressionAfter.toSql(stringBuilder);
//        stringBuilder.append(" AS " + tepTabB);
//        System.out.println("stringBuilder :" + "\n" + stringBuilder.toString() + "\n");
//        System.out.println("selectAfter :" + "\n" + selectAfter.toString() + "\n");
//
//        Select selectJoin = new Select();
//        Column columnThis = new Column(tepTaba + ".profit");
//        Column columnThat = new Column(tepTabB + ".profit");
//        columnThis.as("profit_this");
//        columnThat.as("profit_that");
//        Column columnDate = new Column(tepTaba + ".date");
//        selectJoin.addAll(columnThis, columnThat, columnDate);
//        Expression expression = new Expression(selectBefore);
//        expression.as(tepTaba);
//        selectJoin.add(expression);
//
//        LeftJoin leftJoin = new LeftJoin(stringBuilder.toString(), new Column("a.date"), new Column("b.date"));
//        selectJoin.join(leftJoin);
//        StringBuilder stringBuilder1 = new StringBuilder();
//        selectJoin.toSql(stringBuilder1);
//        System.out.println("stringBuilder1 :" + "\n" + stringBuilder1.toString() + "\n");
//
//
//        System.out.println("selectJoin :" + "\n" + selectJoin.toString() + "\n");
//
//
//        Expression expressionLast = new Expression(selectJoin);
//        expressionLast.as("t");
//
//        Select selectLast = new Select();
//        Column column = new Column("t", "*");
//        selectLast.add(column);
//        selectLast.from(stringBuilder1.toString());
//        String s = selectLast.toString();
//        System.out.println("selectLast :" + "\n" + s + "\r");


    }
}
